"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 导入导出、备份恢复工具支持M*兼容性
Case Name   : gs_dump工具导出兼容M*create table like xx语法
Create At   : 2023.05.04
Owner       : z@wan005
Description :
    1.创建兼容b库
    2.兼容b库下建分区表
    3.使用create table like语句创建新表，源表是分区表
    4.使用gs_dump工具导出表为纯文本格式
    5.导入之前导出的数据到新库
    6.连接导入库查询表
    7.连接导入库查询表
    8.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功，including+参数指复制源表指定信息
    7.成功，excluding+参数指不复制源表指定信息
    8.成功
History     :modified by z@wan005，2023.08.18，修改step5
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class GsDump0009(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.dbuser_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.com = Common()
        self.db_name = "db_dump_0009"
        self.db_name_01 = "db_dump_0009_01"
        self.tb_name = "tb_dump_0009"
        self.tb_name_01 = "tb_dump_0009_01"
        self.tb_name_02 = "tb_dump_0009_02"
        self.dump_path = os.path.join(macro.DB_BACKUP_PATH, 'dump0009')

    def test_server_tools(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '----step1: 创建兼容b库 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility 'b';" \
                  f"drop database if exists {self.db_name_01};" \
                  f"create database {self.db_name_01} dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertEqual(2, sql_result.count(
            self.constant.CREATE_DATABASE_SUCCESS), '执行失败:' + text)

        text = '----step2: 兼容b库下建分区表 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name},{self.tb_name_01}," \
                  f"{self.tb_name_02};" \
                  f"create table {self.tb_name}(c int, index using btree(c))" \
                  f"partition by range(c)" \
                  f"(" \
                  f"partition p1 values less than (1000)," \
                  f"partition p2 values less than (2000)," \
                  f"partition p3 values less than (4000)," \
                  f"partition p4 values less than maxvalue" \
                  f");" \
                  f"create table {self.tb_name_01}(c int default 1000 \
                  check(c > 0), " \
                  f"index using btree(c))" \
                  f"partition by range(c)" \
                  f"(" \
                  f"partition p1 start (0) end (1000)," \
                  f"partition p2 end (2000)," \
                  f"partition p3 start (2000) end (4000)," \
                  f"partition p4 start (4000)" \
                  f");" \
                  f"create table {self.tb_name_02}" \
                  f"(col1 int not null, col2 int comment '第二列')" \
                  f"partition by range(col1) subpartition by range(col2)" \
                  f"(" \
                  f"partition p1 values less than (1000)" \
                  f"(" \
                  f"subpartition p11 values less than (500)," \
                  f"subpartition p12 values less than (1000))," \
                  f"partition p2 values less than (2000)" \
                  f"(" \
                  f"subpartition p21 values less than (1500)," \
                  f"subpartition p22 values less than (2000)" \
                  f")," \
                  f"partition p3 values less than (4000)(" \
                  f"subpartition p31 values less than (3000)," \
                  f"subpartition p32 values less than (4000)" \
                  f")," \
                  f"partition p4 values less than maxvalue" \
                  f"(" \
                  f"subpartition p41 values less than (8000)," \
                  f"subpartition p42 values less than maxvalue));"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(3,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)

        text = '----step3: 使用create table like语句创建新表，源表是分区表' \
               ' expect: 成功----'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name}_04,\
        {self.tb_name}_05,{self.tb_name}_06,{self.tb_name}_07,\
        {self.tb_name}_08,{self.tb_name}_09,{self.tb_name}_10,\
        {self.tb_name}_11,{self.tb_name}_12,{self.tb_name}_13,\
        {self.tb_name}_14,{self.tb_name}_15,{self.tb_name}_16,\
        {self.tb_name}_17,{self.tb_name}_18,{self.tb_name}_19,\
        {self.tb_name}_20,{self.tb_name}_21;
        create table {self.tb_name}_04 like {self.tb_name} \
        including all;
        create table {self.tb_name}_05 like {self.tb_name} including indexes;
        create table {self.tb_name}_06 like {self.tb_name} including partition;
        create table {self.tb_name}_07 like {self.tb_name_01} including \
        defaults;
        create table {self.tb_name}_08 like {self.tb_name_01} \
        including generated;
        create table {self.tb_name}_09 like {self.tb_name_01} \
        including constraints;
        create table {self.tb_name}_10 like {self.tb_name_02} including \
        storage;
        create table {self.tb_name}_11 like {self.tb_name_02} \
        including comments;
        create table {self.tb_name}_12 like {self.tb_name_02} \
        including reloptions;
        create table {self.tb_name}_13 like {self.tb_name} excluding defaults;
        create table {self.tb_name}_14 like {self.tb_name} excluding generated;
        create table {self.tb_name}_15 like {self.tb_name_01} \
        excluding constraints;
        create table {self.tb_name}_16 like {self.tb_name} excluding indexes;
        create table {self.tb_name}_17 like {self.tb_name} excluding storage;
        create table {self.tb_name}_18 like {self.tb_name_02} excluding \
        comments;
        create table {self.tb_name}_19 like {self.tb_name} \
        excluding reloptions;
        create table {self.tb_name}_20 like {self.tb_name} excluding all;
        create table {self.tb_name}_21 like {self.tb_name};'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(18,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)

        text = '----step4：使用gs_dump工具导出表为纯文本格式 expect: 成功----'
        self.log.info(text)
        gs_dump_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gs_dump " \
                      f"-p {self.dbuser_node.db_port} " \
                      f"{self.db_name} -F p " \
                      f"-f {self.dump_path}"
        self.log.info(gs_dump_cmd)
        dump_msg = self.dbuser_node.sh(gs_dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(f"dump database {self.db_name} successfully",
                      dump_msg, '执行失败:' + text)

        text = '----step5：导入之前导出的数据到新库 expect: 成功----'
        self.log.info(text)
        gsql_cmd = f"source {macro.DB_ENV_PATH};" \
                   f"gsql -p {self.dbuser_node.db_port} " \
                   f"{self.db_name_01} -r -f {self.dump_path}"
        self.log.info(gsql_cmd)
        gsql_msg = self.dbuser_node.sh(gsql_cmd).result()
        self.log.info(gsql_msg)
        self.assertEqual(21, gsql_msg.count('CREATE TABLE'),
                         '执行失败:' + text)

        text = '----step6: 连接导入库查询新表信息 expect: 成功，including+参数指复制' \
               '源表指定信息----'
        self.log.info(text)
        sql_cmd = f"show create table {self.tb_name}_04;" \
                  f"show create table {self.tb_name}_05;" \
                  f"show create table {self.tb_name}_06;" \
                  f"show create table {self.tb_name}_07;" \
                  f"show create table {self.tb_name}_08;" \
                  f"show create table {self.tb_name}_09;" \
                  f"show create table {self.tb_name}_10;" \
                  f"show create table {self.tb_name}_11;" \
                  f"show create table {self.tb_name}_12;" \
                  f"show create table {self.tb_name}_13;" \
                  f"show create table {self.tb_name}_14;"
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name_01)
        self.log.info(sql_result)
        msg_lis = ["CREATE INDEX tb_dump_0009_04_c_idx",
                   "CREATE INDEX tb_dump_0009_05_c_idx",
                   "PARTITION BY RANGE (c)", "DEFAULT 1000",
                   "USING btree (c) LOCAL",
                   "CONSTRAINT tb_dump_0009_01_c_check",
                   "SUBPARTITION BY RANGE (col2)",
                   "COMMENT ON COLUMN tb_dump_0009_11.col2 IS '第二列'",
                   "CREATE TABLE tb_dump_0009_12", "USING btree",
                   "PARTITION p1_c_idx"]
        for msg in msg_lis:
            self.assertIn(msg, sql_result, '查询失败' + text)

        text = '----step7: 连接导入库查询新表信息 expect: 成功，excluding+参数指不' \
               '复制源表指定信息----'
        self.log.info(text)
        sql_cmd = f"show create table {self.tb_name}_15;" \
                  f"show create table {self.tb_name}_16;" \
                  f"show create table {self.tb_name}_17;" \
                  f"show create table {self.tb_name}_18;" \
                  f"show create table {self.tb_name}_19;" \
                  f"select relname,relkind from pg_class where " \
                  f"relname='{self.tb_name}_20';" \
                  f"show create table {self.tb_name}_21;"
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name_01)
        self.log.info(sql_result)
        self.assertNotIn('check(c > 0)', sql_result, '查询失败' + text)
        self.assertNotIn('CREATE INDEX tb_dump_0009_16_c_idx', sql_result,
                         '查询失败' + text)
        self.assertIn('CREATE INDEX tb_dump_0009_17_c_idx', sql_result,
                      '查询失败' + text)
        self.assertNotIn('COMMENT ON', sql_result, '查询失败' + text)
        self.assertIn('PARTITION BY RANGE (c)', sql_result, '查询失败' + text)
        self.assertIn('tb_dump_0009_20 | r', sql_result, '查询失败' + text)
        self.assertIn('CREATE INDEX tb_dump_0009_21_c_idx', sql_result,
                      '查询失败' + text)

    def tearDown(self):
        text = '---step8：清理环境 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name_01};"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname='postgres')
        self.log.info(sql_result)
        rm_cmd = f"rm -rf {self.dump_path}"
        self.log.info(rm_cmd)
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        self.assertEqual(2,
                         sql_result.count(self.constant.DROP_DATABASE_SUCCESS))
        self.assertEqual('', rm_msg)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
